<?php
class Blogger_Manager extends Model {
	
	function Blogger_Manager() {
		parent::Model();
		// my custom lib.
		$this->load->library('common');
	}
	
	function GetBloggerList() {
		// alles SQL -_-!
		$qry = $this->db->query('SELECT b.`id`, b.`user_name`, bi.`title`, bi.`slogan`, bi.`entry_num`, (SELECT SUM(e.`comment_num`) FROM `nt_entries` e WHERE e.`state` < 99 AND e.`blogger_id` = b.`id`) AS comment_num, bi.`last_update_time`, b.`user_url` FROM `nt_blogger_info` bi JOIN `nt_bloggers` b ON bi.`blogger_id` = b.`id` WHERE bi.`show` = 1 ORDER BY bi.`last_update_time` DESC, bi.`entry_num` DESC, bi.`reg_date`');
		return $qry->result();
	}
	
	function GetBloggerInfo($blogger_id) {
		$qry = $this->db->query('SELECT b.`id`, b.`user_name`, b.`password`, b.`pwd_hash`, b.`user_url`, b.`email`, bi.`title`, bi.`slogan`, bi.`entry_num`, (SELECT SUM(e.`comment_num`) FROM `nt_entries` e WHERE e.`blogger_id` = ? AND e.`state` < 99) AS comment_num, bi.`announcement`, bi.`skin_name`, bi.`allow_public_comment`, bi.`friend_password`, bi.`language`, bi.`avatar` FROM `nt_blogger_info` bi JOIN `nt_bloggers` b ON bi.`blogger_id` = b.`id` WHERE b.`id` = ?', array($blogger_id, $blogger_id));
		if($qry->num_rows() < 1) {
			throw new Exception('ERROR: No such Blogger!');
		}
		return $qry->row();
	}
	
	function Register($reg_data) {
		$qry = $this->db->query('SELECT * FROM `nt_bloggers` WHERE `user_name` = ? OR `user_url` = ?', array($reg_data['user_name'], $reg_data['user_url']));
		if($qry->num_rows() > 0) {
			return 0;
		}
		
		$parms = array(
			'user_name' => $reg_data['user_name'],
			'password' => $reg_data['password'],
			'pwd_hash' => md5($reg_data['password']), // using md5
			'email' => $reg_data['email'],
			'user_url' => $reg_data['user_url']
		);
		$this->db->insert('nt_bloggers', $parms);
		$new_blogger_id = $this->db->insert_id();
		$parms1 = array(
			'blogger_id' => $new_blogger_id,
			'reg_date' => $reg_data['reg_date'],
			'reg_ip' => $reg_data['reg_ip'],
			'title' => $reg_data['title'],
			'announcement' => ''
		);
		$this->db->insert('nt_blogger_info', $parms1);
		
		return $new_blogger_id;
	}
	
	function ChangePassword($blogger_id, $new_pwd) {
		$new_pwd_hash = md5($new_pwd);
		$this->db->query('UPDATE `nt_bloggers` SET `password` = ?, `pwd_hash` = ? WHERE `id` = ?', array($new_pwd, $new_pwd_hash, $blogger_id));
	}
	
	function ChangeUserURL($blogger_id, $new_user_url) {
		$this->db->query('UPDATE `nt_bloggers` SET `user_url` = ? WHERE `id` = ?', array($new_user_url, $blogger_id));
	}
	
	function ChangeEMail($blogger_id, $new_email) {
		$this->db->query('UPDATE `nt_bloggers` SET `email` = ? WHERE `id` = ?', array($new_email, $blogger_id));
	}
	
	function UpdateBloggerInfo($blogger_info) {
		$parms = array(
			'title' => $blogger_info['title'],
			'slogan' => $blogger_info['slogan'],
			'friend_password' => $blogger_info['friend_password'],
			'announcement' => $blogger_info['announcement'],
			'skin_name' => $blogger_info['skin_name'],
			'allow_public_comment' => $blogger_info['allow_public_comment']
		);
		$this->db->where('blogger_id', $blogger_info['blogger_id']);
		$this->db->update('nt_blogger_info', $parms);
	}
	
	function SetAuthInfo($blogger_id, $pwd, $pwd_is_hash = TRUE, $days = 0, $blogger_data = NULL) {
		// set cookie
		$this->common->SetCookie('blogger_id', $blogger_id, $days);
		if(!$pwd_is_hash) {
			$pwd = md5($pwd);
		}
		$this->common->SetCookie('pwd_hash', $pwd, $days);
		// set session
		$this->common->SetSession('blogger_id', $blogger_id);
		if(!is_null($blogger_data)) {
			$this->common->SetSession('blogger_data', json_encode($blogger_data));
		}
	}
	
	function Logout() {
		$this->common->ClearSession();
		
		delete_cookie('last_entries_list_year_month');
		
		delete_cookie('last_comment_name');
		delete_cookie('last_comment_email');
		delete_cookie('last_comment_url');
		
		delete_cookie('last_pm_sender_name');
		delete_cookie('last_pm_sender_email');
		
		$fr_pwds = $this->common->GetCookie('friend_pwd_hash');
		if($fr_pwds !== FALSE) {
			foreach($fr_pwds as $n => $v) {
				delete_cookie('friend_pwd_hash[' . $n . ']');
			}
		}
		
		delete_cookie('blogger_id');
		delete_cookie('pwd_hash');
	}
	
	function GetBlogCustomCategoryList($blogger_id) {
		$qry = $this->db->query('SELECT `id`, `title`, `description` FROM `nt_categories` WHERE `blogger_id` = ? AND `id` > 1 ORDER BY `title`', array($blogger_id));
		return $qry->result();
	}
	
	function GetBlogArchiveList($blogger_id) {
		$qry = $this->db->query('SELECT YEAR(`post_time`) AS `year`, MONTH(`post_time`) AS `month`, COUNT(`id`) AS `num`, `blogger_id` FROM `nt_entries` WHERE `blogger_id` = ? AND `state` < 99 GROUP BY YEAR(`post_time`), MONTH(`post_time`) ORDER BY YEAR(`post_time`) DESC, MONTH(`post_time`) DESC', array($blogger_id));
		return $qry->result();
	}
	
	function GetDraftList($blogger_id) {
		$qry = $this->db->query('SELECT * FROM `nt_entries` WHERE `blogger_id` = ? AND `state` = 99 ORDER BY `post_time` DESC, `id` DESC', array($blogger_id));
		return $qry->result();
	}
	
	function GetLinks($blogger_id) {
		$qry = $this->db->query('SELECT * FROM `nt_links` WHERE `blogger_id` = ? order by `title`', array($blogger_id));
		return $qry->result();
	}
	
	function GetRecentComments($blogger_id, $num) {
		// faster way
		$qry1 = $this->db->query('SELECT c.`id`, c.`entry_id`, \'\' AS `content`, c.`author_name`, e.`state` AS entry_state FROM `nt_comments` c JOIN `nt_entries` e ON c.`entry_id` = e.`id` WHERE c.`state` = 1 AND e.`state` < 99 AND e.`blogger_id` = ? ORDER BY c.`id` DESC LIMIT ?', array($blogger_id, $num));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			$qry2 = $this->db->query('SELECT `comment_id`, `content` FROM `nt_comment_data` WHERE `comment_id` IN (' . $ids . ') ORDER BY `comment_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_comment_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->comment_id == $curr_comment_id) {
						$r2->content = $ct->content;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	
	function GetRecentComments4RSS($blogger_id, $num) {
		// get comments for public entries only!
		// faster way
		$qry1 = $this->db->query('SELECT c.`id`, c.`entry_id`, \'\' AS `content`, c.`author_name`, c.`post_time`, e.`state` AS entry_state FROM `nt_comments` c JOIN `nt_entries` e ON c.`entry_id` = e.`id` WHERE c.`state` = 1 AND e.`state` = 1 AND e.`blogger_id` = ? ORDER BY c.`id` DESC LIMIT ?', array($blogger_id, $num));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			$qry2 = $this->db->query('SELECT `comment_id`, `content` FROM `nt_comment_data` WHERE `comment_id` IN (' . $ids . ') ORDER BY `comment_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_comment_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->comment_id == $curr_comment_id) {
						$r2->content = $ct->content;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	
	// Get comment num (authed only)
	function GetCommentNum($blogger_id) {
		$qry = $this->db->query('SELECT SUM(`comment_num`) as comment_num FROM `nt_entries` WHERE `blogger_id` = ? AND `state` < 99', array($blogger_id));
		if($qry->num_rows() > 0) {
			return $qry->row()->comment_num;
		} else {
			return 0;
		}
	}
	
	// Recent entries to show on Blog-Front-Page
	function GetRecentEntries($blogger_id, $num) {
		// faster way. Retrieve entry info without Content first
		$qry1 = $this->db->query('SELECT e.`id`, e.`blogger_id`, e.`post_time`, e.`title`, e.`expression`, \'\' AS `content`, e.`comment_num`, e.`state`, e.`category_title`, e.`category_id`, e.`tags` FROM `nt_entries` e WHERE e.`blogger_id` = ? AND e.`state` < 99 ORDER BY e.`post_time` DESC, e.`id` DESC LIMIT ?', array($blogger_id, $num));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			// then retrive content based on ids
			$qry2 = $this->db->query('SELECT `entry_id`, `content` FROM `nt_entry_data` WHERE `entry_id` IN (' . $ids . ') ORDER BY `entry_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_entry_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->entry_id == $curr_entry_id) {
						$r2->content = $ct->content;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	
	// Get a page of entries (Front page paging)
	function GetPageEntries($blogger_id, $page_size, $page_num) {
		// faster way. Retrieve entry info without Content first
		$offset = ($page_num - 1) * $page_size;
		$qry1 = $this->db->query('SELECT e.`id`, e.`blogger_id`, e.`post_time`, e.`title`, e.`expression`, \'\' AS `content`, e.`comment_num`, e.`state`, e.`category_title`, e.`category_id`, e.`tags` FROM `nt_entries` e WHERE e.`blogger_id` = ? AND e.`state` < 99 ORDER BY e.`post_time` DESC, e.`id` DESC LIMIT ?, ?', array($blogger_id, $offset, $page_size));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			// then retrive content based on ids
			$qry2 = $this->db->query('SELECT `entry_id`, `content` FROM `nt_entry_data` WHERE `entry_id` IN (' . $ids . ') ORDER BY `entry_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_entry_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->entry_id == $curr_entry_id) {
						$r2->content = $ct->content;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	
	function GetEntriesByCategory($blogger_id, $cate_id) {
		// faster version
		$qry1 = $this->db->query('SELECT e.`id`, e.`blogger_id`, e.`post_time`, e.`title`, e.`expression`, \'\' AS `content_top`, e.`state`, e.`tags`, e.`comment_num` FROM `nt_entries` e WHERE e.`blogger_id` = ? AND e.`category_id` = ? AND e.`state` < 99 ORDER BY e.`post_time` DESC, e.`id` DESC', array($blogger_id, $cate_id));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			$qry2 = $this->db->query('SELECT `entry_id`, LEFT(`content`, 128) AS `content_top` FROM `nt_entry_data` WHERE `entry_id` IN (' . $ids . ') ORDER BY `entry_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_entry_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->entry_id == $curr_entry_id) {
						$r2->content_top = $ct->content_top;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	// for rss, full content but public entries only
	function GetEntriesByCategory4Feed($blogger_id, $cate_id, $num) {
		// faster way...
		$qry1 = $this->db->query('SELECT e.`id`, e.`blogger_id`, e.`post_time`, e.`title`, e.`expression`, \'\' AS `content`, e.`state`, e.`tags` FROM `nt_entries` e WHERE e.`blogger_id` = ? AND e.`category_id` = ? AND e.`state` = 1 ORDER BY e.`post_time` DESC, e.`id` DESC LIMIT ?', array($blogger_id, $cate_id, $num));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			$qry2 = $this->db->query('SELECT `entry_id`, `content` FROM `nt_entry_data` WHERE `entry_id` IN (' . $ids . ') ORDER BY `entry_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_entry_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->entry_id == $curr_entry_id) {
						$r2->content = $ct->content;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	
	function GetCategoryTitleDescByID($blogger_id, $cate_id) {
		$qry = $this->db->query('SELECT `id`, `title`, `description` FROM `nt_categories` WHERE `blogger_id` = ? AND `id` = ? LIMIT 1', array($blogger_id, $cate_id));
		return $qry->row();
	}
	
	function GetEntriesByMonth($blogger_id, $year, $month) {
		// faster way...
		$qry1 = $this->db->query('SELECT e.`id`, e.`blogger_id`, e.`post_time`, e.`title`, e.`expression`, \'\' AS `content_top`, e.`state`, e.`tags`, e.`comment_num`, e.`category_title` FROM `nt_entry_data` ed JOIN `nt_entries` e ON ed.`entry_id` = e.`id` WHERE e.`blogger_id` = ? AND YEAR(`post_time`) = ? AND MONTH(`post_time`) = ? AND e.`state` < 99 ORDER BY e.`post_time` DESC, e.`id` DESC', array($blogger_id, $year, $month));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			$qry2 = $this->db->query('SELECT `entry_id`, LEFT(`content`, 128) AS `content_top` FROM `nt_entry_data` WHERE `entry_id` IN (' . $ids . ') ORDER BY `entry_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_entry_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->entry_id == $curr_entry_id) {
						$r2->content_top = $ct->content_top;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	// for exporting, public & secret entries with full content
	function GetEntriesByMonth4Exp($blogger_id, $year, $month) {
		// faster...
		$qry1 = $this->db->query('SELECT e.`id`, e.`blogger_id`, e.`post_time`, e.`title`, e.`expression`, \'\' AS `content`, e.`state`, e.`tags` FROM `nt_entries` e WHERE e.`blogger_id` = ? AND YEAR(`post_time`) = ? AND MONTH(`post_time`) = ? AND e.`state` < 99 ORDER BY e.`post_time` DESC', array($blogger_id, $year, $month));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			$qry2 = $this->db->query('SELECT `entry_id`, `content` FROM `nt_entry_data` WHERE `entry_id` IN (' . $ids . ') ORDER BY `entry_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_entry_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->entry_id == $curr_entry_id) {
						$r2->content = $ct->content;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	
	function GetUnauthedComments($blogger_id) {
		// faster...
		$qry1 = $this->db->query('SELECT c.`id`, c.`entry_id`, \'\' AS `content`, c.`author_name`, c.`email`, c.`url`, c.`post_time`, c.`ip`, e.`title` FROM `nt_comments` c JOIN `nt_entries` e ON c.`entry_id` = e.`id` WHERE c.`state` = 0 AND e.`blogger_id` = ? ORDER BY c.`id` DESC', array($blogger_id));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			$qry2 = $this->db->query('SELECT `comment_id`, `content` FROM `nt_comment_data` WHERE `comment_id` IN (' . $ids . ') ORDER BY `comment_id` DESC');
			$coll_contents = $qry2->result();
			foreach($retv as $r2) {
				$curr_comment_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->comment_id == $curr_comment_id) {
						$r2->content = $ct->content;
						break;
					}
				}
			}
			return $retv;
		}
		return $qry1->result();
	}
	
	function AuthComments($comment_ids) {
		// update comment states
		$str_ids = implode(', ', $comment_ids);
		$this->db->query('UPDATE `nt_comments` SET `state` = 1 WHERE `id` IN (' . $str_ids . ')');
		// update comment_num field of t_entries
		foreach($comment_ids as $cid) {
			$this->db->query('UPDATE `nt_entries` SET `comment_num` = `comment_num` + 1 WHERE `id` = (SELECT c.`entry_id` FROM `nt_comments` c WHERE c.`id` = ?)', array($cid));
		}
	}
	
	// unauthed comments don't affect comment_num field, so can be deleted directly
	function DeleteUnauthedComments($comment_ids) {
		$str_ids = implode(', ', $comment_ids);
		$this->db->query('DELETE FROM `nt_comment_data` WHERE `comment_id` IN (' . $str_ids . ')');
		$this->db->query('DELETE FROM `nt_comments` WHERE `id` IN (' . $str_ids . ')');
	}
	
	// for Portal-Page
	function GetLatestEntries($num) {
		// faster version...
		$qry1 = $this->db->query('SELECT e.`id`, e.`blogger_id`, e.`post_time`, e.`title`, e.`author_name`, e.`expression`, e.`tags`, e.`comment_num`, b.`user_url`, \'\' AS `content` FROM `nt_entries` e JOIN (`nt_blogger_info` bi JOIN `nt_bloggers` b ON bi.`blogger_id` = b.`id`) ON e.`blogger_id` = bi.`blogger_id` WHERE e.`state` = 1 AND bi.`show` = 1 ORDER BY e.`post_time` DESC, e.`id` DESC LIMIT ?', array($num));
		if($qry1->num_rows() > 0) {
			$retv = $qry1->result();
			$ids = array();
			foreach($retv as $r1) {
				array_push($ids, $r1->id);
			}
			$ids = implode(', ', $ids);
			
			$qry2 = $this->db->query('SELECT `entry_id`, `content` FROM `nt_entry_data` WHERE `entry_id` IN (' . $ids . ') ORDER BY `entry_id` DESC');
			$coll_contents = $qry2->result();
			
			foreach($retv as $r2) {
				$current_entry_id = $r2->id;
				foreach($coll_contents as $ct) {
					if($ct->entry_id == $current_entry_id) {
						$r2->content = $ct->content;
						break;
					}
				}
			}
			
			return $retv;
		}
		return $qry1->result();
	}
	
	// for front-page
	function GetEntriesToday() {
		// TODO
	}
	
	// List a Blogger's uploaded avatars as an array of filenames or FALSE;
	function GetUserAvatars($blogger_id) {
		$avts = array();
		$user_avatar_path = $_SERVER['DOCUMENT_ROOT'] . 'img/avatars/' . $blogger_id;
		if(is_dir($user_avatar_path)) {
			// read it out
			if($dh = opendir($user_avatar_path)) {
				while(($file = readdir($dh)) !== FALSE) {
					if(!is_dir($user_avatar_path . '/' . $file)) {
						array_push($avts, $file);
					}
				}
			} else {
				throw new Exception('Cannot read User-Avatars folder!');
			}
			return $avts;
		} else {
			return NULL;
		}
	}
	
	function ListAllEmoTags($per_page, $page_no) {
		$start = $per_page * $page_no;
		$qry = $this->db->query('SELECT e.`post_time`, e.`emo_icon`, e.`emo_text`, b.`user_name` FROM `nt_emotags` e JOIN `nt_bloggers` b ON e.`blogger_id` = b.`id` ORDER BY e.`post_time` DESC LIMIT ?, ?', array($start, $per_page));
		return $qry->result();
	}
	
	function CountAllEmoTags() {
		$qry = $this->db->query('SELECT COUNT(`id`) AS `num` FROM `nt_emotags`');
		return $qry->row()->num;
	}
	
}
?>